ALTER VIEW [dbo].[vw_Fab_Wip_View] AS(
SELECT  
	LT.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER,FAB_LOT_NUMBER, ISNULL(LOT_CATEGORY,'PROD') AS LOT_CATEGORY, 
	WAFER_QTY_IN, DATE_IN,STAGE_NAME, STAGE_POSITION,LOT_CURRENT_STATUS,FL_EXPECTED_DATE_OUT, 
	convert(int,cast((SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Gross Die/Wafer' AND PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float)) AS GROSS_DIE_WAFER,
	FL_OUTLOOK_DATE,WAFER_ID, PART_NUMBER,
	(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
		CASE 
			WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
		END 
	) AS BASE_PRODUCT,
	(CASE 
		WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
		WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
	END) AS BASE_PRODUCT_ID, PS_TYPE_NAME, PS_TYPE, 1 AS PS_TYPE_ID, LT.LOT_ID, LT.LOCATION_ID
FROM 
	LOT_TABLE LT, FAB_LOT FL, LOCATION L, PRODUCT P, PROCESS_STEP PS
WHERE 
	LT.LOT_ID = FL.LOT_ID 
	AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
	AND LT.LOCATION_ID = L.LOCATION_ID
	AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
	AND WAFER_QTY_IN > 0
	AND ( LOT_CURRENT_STATUS = 'Running' OR LOT_CURRENT_STATUS = 'Hold' )
	AND P.OBSOLETE != 0
	AND PS_TYPE_Name = 'Fab'
)
